"""
`th_add.py`
## 主要功能：
- 创建数据库
- 将原始数据`.txt`文件中的数据存储进入数据库
## 说明：
- 采用阿里云数据库
- 为了数据安全，请使用账户：`lhc0531`对数据库进行操作，其他账户不具有权限
- 为了存储速度，采用`threading`多线程方式

> author: lhc
"""

import pymysql


class database:
    def __init__(self):
        self.DB_port = 'rm-m5ei41q6w870w5413ho.mysql.rds.aliyuncs.com'
        self.DB_user = 'visiter'
        self.DB_passwd = '@visiter123456'

    def Create_conn(self):
        conn = pymysql.connect(host=self.DB_port, user=self.DB_user, passwd=self.DB_passwd)
        return conn

    def Connect_table(self):
        conn = pymysql.connect(host=self.DB_port, user=self.DB_user, passwd=self.DB_passwd, db="didi_data")
        return conn

    def InitDB(self):
        """
        初始化数据库，清除所有数据
        :return:
        """
        try:
            conn = self.Create_conn()
            cursor = conn.cursor()  # 获取游标
            sql = 'drop database if exists didi_data'
            cursor.execute(sql)
            conn.commit()  # 提交
            cursor.close()  # 关闭游标
            conn.close()  # 退出连接
        except:
            self.Create_DB()
        self.Create_DB()

    def Create_DB(self):
        conn = self.Create_conn()
        cursor = conn.cursor()
        # 创建pythonBD数据库
        cursor.execute('CREATE DATABASE IF NOT EXISTS didi_data DEFAULT CHARSET utf8 COLLATE utf8_general_ci;')
        conn = self.Connect_table()
        cursor = conn.cursor()  # 获取游标
        # 创建信息表
        #
        sql = """
        create table if not exists dwv_haikou_1(
            order_id char (128) primary key,
            product_id char (64),
            city_id char (64),
            district char (64),
            county char (64),
            didi_type char (8),
            combo_type char (8),
            traffic_type char (8),
            passenger_count char (8),
            driver_product_id char (8),
            start_dest_distance char (64),
            arrive_time char (64),
            departure_time char (64),
            pre_total_fee char (64),
            normal_time char (64),
            bubble_trace_id char (128),
            product_1level int(8),
            dest_lng char (64),
            dest_lat char (64),
            starting_lng char (64),
            starting_lat char (64),
            year char (16),
            month char (16),
            day char (16)
        );"""
        cursor.execute(sql)

    def add_data1(self, args):
        """
        添加 dwv_haikou_2
        :param args: 按照顺序分别是
        :return:
        """
        conn = self.Connect_table()
        cursor = conn.cursor()  # 获取游标
        sql = 'insert into didi_data.dwv_haikou_2 value (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s);'
        cursor.execute(sql, args)
        conn.commit()  # 提交
        cursor.close()  # 先关闭游标
        conn.close()  # 再关闭数据库连接


def add(n, num):
    db = database()
    f = open("data/dwv_order_make_haikou_2.txt", "r", encoding='utf-8')
    f.readline()
    i = 0
    for m in range(n):
        f.readline()
        i += 1

    while True:
        data = f.readline().split()

        try:
            data[11] = str(data[11] + '-' + data[12])
            data.pop(12)
            data[12] = str(data[12] + '-' + data[13])
            data.pop(13)

            for k in range(len(data)):
                if data[k] == 'NULL':
                    data[k] = 'none'
            db.add_data1(tuple(data))
        except:
            print('error', n)
        else:
            print('finish--', i)
            i += 1
            for m in range(num):
                f.readline()
                i += 1

        if not data:
            print("Finish")
            break


if __name__ == '__main__':
    import threading

    for i in range(10):
        threading.Thread(target=add, args=(i, 10,)).start()
